package dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import dao.ProductDao;
import domain.Order;
import domain.Product;
import domain.User;
import util.DataSourceUtils;

public class ProductDaoImpl implements ProductDao {

	@Override
	public List<Product> findAllProducts(int page, int limit) throws SQLException {
		String sql = "select products.* from products limit ?,?;";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Product>> () {

			@Override
			public List<Product> handle(ResultSet rs) throws SQLException {
				List<Product> products = new ArrayList<Product>();
				
				while (rs.next()) {
					Product p = new Product();
					
					p.setCategory(rs.getString("products.category"));
					p.setDescription(rs.getString("products.description"));
					p.setId(rs.getString("products.id"));
					p.setImgurl(rs.getString("products.imgurl"));
					p.setName(rs.getString("products.name"));
					p.setPnum(rs.getInt("products.pnum"));
					p.setPrice(rs.getDouble("products.price"));
					
					products.add(p);
				}
				
				return products;
			}
			
		}, (page - 1) * limit, limit);
	}

	@Override
	public boolean addProduct(Product product) throws SQLException {
		String sql = "insert into products(id,name,price,category,pnum,imgurl,description) values(?,?,?,?,?,?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, product.getId(), product.getName(), product.getPrice(), product.getCategory(), product.getPnum(), product.getImgurl(), product.getDescription());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteProduct(String id) throws SQLException {
		String sql = "delete from products where id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, id);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public Product findProductById(String id) throws SQLException {
		String sql = "select products.* from products where id=?;";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<Product> () {

			@Override
			public Product handle(ResultSet rs) throws SQLException {
				Product p = new Product();
				
				while (rs.next()) {
					p.setCategory(rs.getString("products.category"));
					p.setDescription(rs.getString("products.description"));
					p.setId(rs.getString("products.id"));
					p.setImgurl(rs.getString("products.imgurl"));
					p.setName(rs.getString("products.name"));
					p.setPnum(rs.getInt("products.pnum"));
					p.setPrice(rs.getDouble("products.price"));
				}
				
				return p;
			}
			
		}, id);
	}

	@Override
	public boolean updateProduct(Product product) throws SQLException {
		String sql = "update products set name=?,price=?,category=?,pnum=?,imgurl=?,description=? where id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, product.getName(), product.getPrice(), product.getCategory(), product.getPnum(), product.getImgurl(), product.getDescription(), product.getId());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<Product> findAllProductsByName(String name, int page, int limit) throws SQLException {
		String sql = "select products.* from products where name like ? limit ?,?;";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		return runner.query(sql, new ResultSetHandler<List<Product>> () {

			@Override
			public List<Product> handle(ResultSet rs) throws SQLException {
				List<Product> products = new ArrayList<Product>();
				
				while (rs.next()) {
					Product p = new Product();
					
					p.setCategory(rs.getString("products.category"));
					p.setDescription(rs.getString("products.description"));
					p.setId(rs.getString("products.id"));
					p.setImgurl(rs.getString("products.imgurl"));
					p.setName(rs.getString("products.name"));
					p.setPnum(rs.getInt("products.pnum"));
					p.setPrice(rs.getDouble("products.price"));
					
					products.add(p);
				}
				
				return products;
			}
			
		}, "%" + name + "%", (page - 1) * limit, limit);
	}

	@Override
	public boolean updatePnumOfProduct(Product product) throws SQLException {
		String sql = "update products set pnum=? where id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, product.getPnum(), product.getId());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

}
